import pymysql
import config as cf
import json
import numpy as np
import datetime

'''
类的专有方法：
__init__ : 构造函数，在生成对象时调用
__del__ : 析构函数，释放对象时使用
__repr__ : 打印，转换
__setitem__ : 按照索引赋值
__getitem__: 按照索引获取值
__len__: 获得长度
__cmp__: 比较运算
__call__: 函数调用
__add__: 加运算
__sub__: 减运算
__mul__: 乘运算
__div__: 除运算
__mod__: 求余运算
__pow__: 乘方
'''


class MySqlOp(object):
    # 定义私有属性,私有属性在类外部无法直接进行访问
    __dbcnn = 0

    def __init__(self, host=cf.MYSQL_HOST, port=cf.MYSQL_PORT, username=cf.MYSQL_USER, password=cf.MYSQL_PWD,
                 db=cf.MYSQL_DB):
        self.host = host
        self.port = port
        self.username = username
        self.password = password
        self.db = db
        # self.__dbcnn = pymysql.connect(host=self.host, port=self.port, user=self.username, passwd=self.password, db=self.db)
        print("========__init__=====================")

    def  __del__(self):
        #self.__dbcnn.close()
        print("========__del__=======关闭数据库==============")


    def dataCmfInsertDb(self, data, hisdata):
        connection = pymysql.connect(host=self.host, port=self.port, user=self.username, passwd=self.password, db=self.db)
        #cur = db.cursor()
        try:
            with connection.cursor() as cursor:
                # 数据
                #data =  [['000001', '平安银行', '2023-12-21', '11', '22', '33', '44', '55', '66', '77','88','99','11','22','33','44', '55'], ]
                #data =  [['000001', '平安银行', '2023-12-21',]]
                # 创建SQL插入语句
                # sql = "INSERT INTO stock_cmjzd ( gpdm, gpmc, sdate, qsjzd, jsjzd, hlbl, pjcb, jscbd, jscbg, qscbd, qscbg, zsz, ltsz, zgb, ltgb, sssj, gshy) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                #sql = "INSERT INTO stock_cmjzd ( gpdm, gpmc, sdate, qsjzd, jsjzd, hlbl, pjcb, jscbd, jscbg, qscbd, qscbg, zsz, ltsz, zgb, ltgb, sssj, gshy) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
                sql = "INSERT INTO stock_cmjzd ( gpdm, gpmc, zgb,ltgb,zsz,ltsz,gshy,sssj, sdate, hlbl, pjcb,jscbd, jscbg,jsjzd,qscbd, qscbg,qsjzd ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"


                # 执行批量插入
                cursor.executemany(sql, [data])
                #cursor.executemany('INSERT INTO stock_cmjzd ( gpdm, gpmc, sdate ) VALUES (%s, %s, %s)', data)
                #cursor.execute('INSERT INTO stock_cmjzd (gpdm, gpmc, sdate) VALUES ("11", "22", "33")')

                sql = "INSERT INTO stock_histrade ( gpdm, gpmc, sdate, kpjg, spjg, zgjg, zdjg, cjl, cje, zf, zdf, zde, hsl,fbbl,sly,bly) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s  )"
                # 执行批量插入
                cursor.executemany(sql, hisdata)
                # print(hisdata)

                # 提交事务
                connection.commit()

        except Exception as e:
            print("dataCmfInsertDb================Error occurred:", e)
        finally:
            # 关闭连接
            connection.close()
            print("========finally=======关闭数据库==============")




    def dataSpotInsertDb(self, data ):
        connection = pymysql.connect(host=self.host, port=self.port, user=self.username, passwd=self.password, db=self.db)
        #cur = db.cursor()
        try:
            print("============------------------==============")
            # print(data)
            with connection.cursor() as cursor:
                current_date = datetime.date.today()
                sdate = current_date.strftime("%Y-%m-%d")
                # sql = "delete from stock_spottrade where sdate='"+sdate+"'"
                sql = "delete from stock_spottrade where sdate='{}'".format(sdate)
                cursor.execute(sql)

                # 数据
                sql = "INSERT INTO  stock_spottrade ( gpdm, gpmc, sdate, zxjg, zdf, zde, cjl, cje, zf, zgj, zdj, jkj, zsj,lb, hsl, syl, sjl, zsz, ltsz, zs, wzdf, lzdf, nzdf  ) VALUES( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )"
                # 执行批量插入
                cursor.executemany(sql, data)
                # 提交事务
                connection.commit()
        except Exception as e:
            print("dataSpotInsertDb================Error occurred:", e)
        finally:
            # 关闭连接
            connection.close()
            print("========finally=======实时数据入库关闭数据库==============")


    def dataCmjzdHisInsertDb(self, data, sdate ):
        connection = pymysql.connect(host=self.host, port=self.port, user=self.username, passwd=self.password, db=self.db)
        #cur = db.cursor()
        try:
            print("============---------dataCmjzdHisInsertDb---------==============")
            with connection.cursor() as cursor:
                current_date = datetime.date.today()
                # sdate = current_date.strftime("%Y-%m-%d")
                # sql = "delete from stock_spottrade where sdate='"+sdate+"'"
                # sql = "delete from stock_cmjzd_his where sdate = %s and gpdm=%s "
                # cursor.executemany(sql, sdate)

                # 数据
                sql = "INSERT INTO  stock_cmjzd_his ( gpdm, gpmc, sdate, hlbl, pjcb,jscbd,jscbg,jsjzd,qscbd,qscbg,qsjzd  ) VALUES( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )"
                # 执行批量插入
                cursor.executemany(sql, data)
                # 提交事务
                connection.commit()
        except Exception as e:
            print("dataCmjzdHisInsertDb================Error occurred:", e)
        finally:
            # 关闭连接
            connection.close()
            print("===dataCmjzdHisInsertDb=====finally=======实时数据入库关闭数据库==============")



    # 增删改
    def operate(self, sql):
        db = pymysql.connect(host=self.host, port=self.port, user=self.username, passwd=self.password, db=self.db)
        cur = db.cursor()
        try:
            # 执行sql语句
            cur.execute(sql)
            op_id = cur.lastrowid
            cur.close()
            # 提交到数据库执行
            db.commit()
        except Exception as e:
            print(e)

            op_id = None

            cur.close()
            # Rollback in case there is any error
            db.rollback()
        # 关闭数据库连接
        db.close()

        return op_id

    # 查
    def select(self, sql):
        db = pymysql.connect(host=self.host, port=self.port, user=self.username, passwd=self.password, db=self.db)
        cur = db.cursor()
        results = None
        try:
            # 执行sql语句
            cur.execute(sql)
            # 获取所有记录列表
            results = cur.fetchall()
            # print(results)
        except Exception as e:
            print(e)
            # 关闭数据库连接
        db.close()
        return results

    @classmethod
    def escape_str(cls, text):
        '''
        string类型数据导入时有可能出现单双引号等需要转义的字段，也可能出现nan这样的字段，需要先处理一下
        :return:
        '''
        if cls.isNaNo(text):
            return 'null'
        else:
            return json.dumps(str(text), ensure_ascii=False)

    @classmethod
    def escape_num(cls, text):
        '''
        转一下整数，报错的话说明传入的不是数字，有sql注入风险
        :return:
        '''
        if str(text) == '0':
            return '0'
        elif cls.isNaNo(text):
            return 'null'
        else:
            # return json.dumps(str(text), ensure_ascii=False)
            try:
                int(text)
                return str(text)
            except Exception as e:
                raise Exception('传入不是数字，有sql注入风险')

        # if cls.isNaNo(text):
        #     return 'null'
        # else:
        #     return json.dumps(str(text), ensure_ascii=False)

    @classmethod
    def isNaNo(cls, sth):
        '''
        NaN、None或者空字符串返回True，其他情况返回False
        '''
        if not sth:
            return True
        if isinstance(sth, float):
            if np.isnan(sth):
                return True
        return False


if __name__ == '__main__':
    print("======================start=====================")
    # 创建数据库连接
    msq = MySqlOp()

    msq.dataCmfInsertDb("test")



    # 执行sql
    #book = msq.select('''show tables;''')
    #print( book)  # (('id', 'int', None, 'NO', 'PRI', None, 'auto_increment', 'select,insert,update,references', ''), ('ruid', 'varchar(255)', 'utf8_unicode_ci', 'YES', '', None, '', 'select,insert,update,references', ''), ('title', 'varchar(255)', 'utf8_unicode_ci', 'YES', '', None, '', 'select,insert,update,references', ''), ('author', 'varchar(255)', 'utf8_unicode_ci', 'YES', '', None, '', 'select,insert,update,references', ''))

    # res = msq.select(f'''select id, src_ruid from book''')
    # for book in res:
    #     id_ = book[0]
    #     src_ruid = book[1]
    #
    #     print(id_)
    #
    #     src_ruid = src_ruid.replace('http:www.bookschina.com', '')
    #
    #     msq.operate(f'''update book set src_ruid={src_ruid} where id={id_}''')


